Reading/Writing Text Files

Creating a Text File and Writing Data

This section introduces operations for creating a new text file or opening an existing text file and writing data into it.

[Excel VBA] In Excel VBA, you can use the Open statement to open a specified text file and write data into it. The syntax of the Open statement is as follows:

Open pathname For Output As filenumber [Len = buffersize]

After opening the text file, you can use the Print statement to write data. Once a text file is opened, it must be closed with the Close statement before reopening it for another type of operation.

Sub SaveToFile()
    Dim intFileNum As Integer
    Dim strFile As String
    Dim strText As String

    strFile = "D:\test.txt"
    strText = "Excel VBA" & vbCrLf & "Python xlwings"
    intFileNum = FreeFile()    'Find an unused file number

    If strFile <> "" Then      'If a non-empty file name is specified
        'Open the file for writing
        Open strFile For Output As #intFileNum
        'Write the content to the file
        Print #intFileNum, strText
    End If

    'Close the file
    Close #intFileNum
End Sub

Running this procedure opens the text file test.txt under drive D, with the effect shown in Figure 3-1.

Figure 3-1 Creating a text file and writing data

[Python] In Python, you can use the open() function to read from and write to text files. The open() function opens a text file in the specified mode and returns a file object. Its syntax is as follows:

open(file, mode='r', buffering=-1, encoding=None, errors=None, \
newline=None, closefd=True, opener=None)

The meanings of the parameters are as follows:

file: Required parameter, specifies the path and name of the text file.

mode: Optional parameter, specifies the opening mode of the text file, including read, write, append, etc.

buffering: Optional parameter, sets buffering (does not affect the result).

encoding: Optional parameter, sets the encoding method, generally UTF-8.

errors: Optional parameter, specifies how to handle encoding and decoding errors, applicable to text mode.

newline: Optional parameter, specifies newline handling in text mode.

closefd: Optional parameter, specifies the type of the file parameter passed in.

opener: Optional parameter, sets a custom file opening method; defaults to None.

Note: After using open() to open a text file, you must ensure the file object is closed after operations. Use the close() function to close the file object.

When open() opens a file, it returns a file object. Methods of this object can be used for reading, writing, truncating, and closing the file, as shown in Table 3-1.

Table 3-1 Methods of the file object

When using open() with a mode value from Table 3-2, a new file will be created if it does not exist.

Table 3-2 Mode settings for writing text files

Example: Create a text file test.txt under drive D.

>>> f = open('D:\\test.txt', 'w')

Using the write() method of the file object to write data:

>>> f.write('Excel VBA\nPython xlwings')

The return value 11 indicates the length of the string written to test.txt.

Opening test.txt immediately after writing shows nothing because the file hasn’t been flushed/closed yet. Use close() to close the file:

>>> f.close()

Now opening the file shows the written string, as in Figure 3-1.

Using a with statement to open and write to a file ensures the file is closed automatically after execution, without needing close():

>>> with open('D:\\test.txt', 'w') as f:
... f.write('Excel VBA\nPython xlwings')

Opening the file shows the previous content was deleted and replaced with the new string.

Using writelines() with a list and newline characters writes multiple lines at once:

>>> f = open('D:\\test.txt', 'w')
>>> f.writelines(['Excel VBA\n', 'Python xlwings'])
>>> f.close()

Opening the file shows the two list elements written on separate lines.

Writing multiple lines in a loop (using \r\n for carriage return + line feed):

>>> f = open('D:\\test.txt', 'w')
>>> for i in range(10):
... f.write('Hello Python!\r\n')
>>> f.close()

Opening the file shows ten lines of Hello Python!.

Reading Text Files

Section 3.1.1 covered creating or opening a text file and writing data. This section explains how to read data from a text file.

[Excel VBA] In Excel VBA, use the Open statement with For Input to read data from a text file. The file must exist, or an error will occur. You can use Input, Line Input, or related statements to read data. The file must be closed with Close before reopening for another operation.

Example:

Sub OpenFile()
    Dim intFileNum As Integer
    Dim LinesFromFile As String
    Dim NextLine As String
    Dim strFile As String

    strFile = "D:\test.txt"
    intFileNum = FreeFile()

    If strFile <> "" Then
        Open strFile For Input As #intFileNum
        Do Until EOF(intFileNum)
            Line Input #intFileNum, NextLine
            LinesFromFile = LinesFromFile & NextLine & Chr(13) & Chr(10)
        Loop
    End If

    Debug.Print LinesFromFile
    Close #intFileNum
End Sub

Running this procedure prints the file’s content in the Immediate Window. See OpenFile2 and OpenFile3 in the same module for examples using Input and Input$.

[Python] When using open() to read a text file, set mode to values in Table 3-3.

Table 3-3 Mode settings for reading text files

Example: Open test.txt in read-only mode:

>>> f = open('D:\\test.txt', 'r')
>>> f.read()
'Excel VBA\n Python xlwings'

Attempting to write to a file opened in read-only mode causes an error:

>>> f.write('This is a test.')

Traceback (most recent call last):

  File "<pyshell#32>", line 1, in <module>
    f.write('This is a test.')
io.UnsupportedOperation: not writable
>>> f.close()

Using readline() to read line by line:

>>> f = open('D:\\test.txt', 'r')
>>> f.readline()
'Hello Python!\n'
>>> f.readline()
'\n'
>>> f.readline(5)
'Hello'
>>> f.close()

Using readlines() to read all lines:

>>> f = open('D:\\test.txt', 'r')
>>> f.readlines()
['Hello Python!\n', '\n', 'Hello Python!\n', ... ]
>>> f.close()

Appending Data to a Text File

Section 3.1.1 showed that opening an existing file for writing deletes its original content. This section discusses appending data while preserving existing content.

[Excel VBA] In Excel VBA, use Open … For Append to append data. Example:

Sub AppendFile()
    Dim intFileNum As Integer
    Dim strFile As String
    Dim strText As String

    strFile = "D:\test.txt"
    strText = "Do it"
    intFileNum = FreeFile()

    If strFile <> "" Then
        Open strFile For Append As #intFileNum
        Print #intFileNum, strText
    End If

    Close #intFileNum
End Sub

Running this appends the new string to the end of the file, keeping the original content.

[Python] Use mode values from Table 3-4 to append data.

Table 3-4 Mode settings for appending to text files

Example:

>>> f = open('D:\\test.txt', 'a')
>>> f.write('Python')
>>> f.close()

Opening the file shows the new content appended after the original data.